﻿CREATE PROCEDURE [dbo].[sp_Get_PowerName]
	@assignment varchar(255),
	@city varchar(255),
	@number int
AS
	SET NOCOUNT ON
	
	if @city is not null
		SELECT TOP (@number) p.[FirstName]
		  ,p.[LastName]
		  ,p.[FirmName]
		  ,p.[BusResCity]
		  ,p.[Occupation]
		  ,p.[Tenure]
		  ,p.[BusResZIP]
		  ,p.[Division]
		  ,p.[Market]
		  ,p.[Terr]
		  FROM [dbo].[PowerName] p
		  where p.BusResCity = @city
		  order by Tenure desc
	else
		SELECT TOP (@number) p.[FirstName]
      ,p.[LastName]
      ,p.[FirmName]
      ,p.[BusResCity]
      ,p.[Occupation]
      ,p.[Tenure]
      ,p.[BusResZIP]
      ,p.[Division]
      ,p.[Market]
      ,p.[Terr]
      FROM [dbo].[PowerName] p
      where p.BusResZIP in (select distinct ZipCode from ZipInfo where AgentName = @assignment)
      and p.BusResCity not in (select distinct City from ZipInfo where AgentName = @assignment)